--First, drop the stored procedure if it exists
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'get_allowed_list')
    BEGIN
        PRINT 'Dropping Procedure get_allowed_list'
        DROP PROCEDURE  get_allowed_list
    END
    PRINT 'Creating Procedure get_allowed_list'
GO

/*
Stored Procedure
Name: get_allowed_list
Parameters:
	@pageNumber int The page number to retrieve
	@recordsPerPage int The records per page to use when calculating pagination
	@NPAFilter smallint The NPA filter to use when getting results
	@stateFilter char(2) The state filter to use when getting results
	@totalResults int output
Return codes:
    0: Success
    1: Invalid parameters
*/
CREATE PROCEDURE get_allowed_list
	@pageNumber int = 1 output,
	@recordsPerPage int = 1000,
	@NPAFilter smallint = null,
	@stateFilter char(2) = null,
	@totalResults int output
AS
BEGIN
	--Starting and ending row numbers for the page
	DECLARE @startRecordNumber bigint
	DECLARE @endRecordNumber bigint
    
    --Validate inputs
    IF(@pageNumber IS NULL OR @pageNumber < 1 OR @recordsPerPage IS NULL OR @recordsPerPage < 1 OR (@NPAFilter IS NOT NULL AND (@NPAFilter < 201 OR @NPAFilter > 999)))
    BEGIN
        RETURN 1
    END

    --If we are not filtering at all
    IF @stateFilter IS NULL AND @NPAFilter IS NULL
    BEGIN
        --Determine the total size of the allowed list
        SELECT @totalResults = COUNT(1) FROM Config.AllowedNPANXX
        SELECT @totalResults = COUNT(1) + @totalResults FROM Config.AllowedAni
        
        --Calculate the starting record number
        SET @startRecordNumber = ((@pageNumber - 1) * @recordsPerPage) + 1

        --If the start record number is past the total number of results returned
        IF(@startRecordNumber > @totalResults)
        BEGIN
            --Set the starting record number to the first record on the last page
            set @pageNumber = ceiling(cast(@totalResults as float) / @recordsPerPage)
            set @startRecordNumber = (@pageNumber - 1) * @recordsPerPage + 1
        END
        
        --Calculate the end record number. This might still be past the end of the record set, but it doesn't matter.
        SET @endRecordNumber = @pageNumber * @recordsPerPage

        --Get all the allowed rows
        ;WITH ALLOWED_LIST AS
        (
            --We do not need more rows than @endRecordNumber
            SELECT
            ROW_NUMBER() OVER (ORDER BY NPA, NXX, XXXX) AS ROW_NUMBER
            ,NPA
            ,NXX
            ,XXXX
            FROM
            (
                --Get entries from both allowed exchanges and allowed ANIs
                (
                    SELECT NPA, NXX, NULL as XXXX
                    FROM Config.AllowedNPANXX
                )
                UNION ALL
                (
                    SELECT NPA, NXX, XXXX
                    FROM Config.AllowedAni
                )
            ) ALLOWED_LIST
        )
        
        SELECT ALLOWED_LIST.NPA
        ,ALLOWED_LIST.NXX
        ,ALLOWED_LIST.XXXX
        ,TD.CityName as CITY
        ,TD.StateAbbr as STATE
        FROM
        ALLOWED_LIST
        JOIN TelcoData.NPANXX TD ON (ALLOWED_LIST.NPA = TD.NPA AND ALLOWED_LIST.NXX = TD.NXX AND (ROW_NUMBER BETWEEN @startRecordNumber AND @endRecordNumber))
    END
    --If we're filtering by NPA, but not by state, then we don't have to join to TelcoData right away
    ELSE IF(@stateFilter IS NULL)
    BEGIN
    
        --Determine the total size of the allowed list
        SELECT @totalResults = COUNT(1)
        FROM Config.AllowedNPANXX WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
        
        SELECT @totalResults = (COUNT(1) + @totalResults)
        FROM Config.AllowedAni WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
        
        --Calculate the starting record number
        SET @startRecordNumber = ((@pageNumber-1) * @recordsPerPage) + 1

        --If the start record number is past the total number of results returned
        IF(@startRecordNumber > @totalResults)
        BEGIN
            --Set the starting record number to the first record on the last page
            set @pageNumber = ceiling(cast(@totalResults as float) / @recordsPerPage)
            set @startRecordNumber = (@pageNumber - 1) * @recordsPerPage + 1
        END
        
        --Calculate the end record number. This might still be past the end of the record set, but it doesn't matter.
        SET @endRecordNumber = @pageNumber * @recordsPerPage
    
        --Now get just the page that we need
        SELECT ALLOWED_LIST_ROWS.NPA
        ,ALLOWED_LIST_ROWS.NXX
        ,ALLOWED_LIST_ROWS.XXXX
        ,TD.CityName AS CITY
        ,TD.StateAbbr AS STATE
        FROM
        (
            --Get the filtered list and assign row numbers to it
            SELECT ROW_NUMBER() OVER (ORDER BY ALLOWED_LIST.NPA, ALLOWED_LIST.NXX, XXXX) AS ROW_NUMBER
            ,ALLOWED_LIST.NPA
            ,ALLOWED_LIST.NXX
            ,ALLOWED_LIST.XXXX
            FROM
            (
                (
                    SELECT NPA, NXX, null as XXXX
                    FROM Config.AllowedNPANXX
                    WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
                )
                UNION ALL
                (
                    SELECT NPA, NXX, XXXX
                    FROM Config.AllowedAni
                    WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
                )
            ) ALLOWED_LIST

        ) ALLOWED_LIST_ROWS
        JOIN TelcoData.NPANXX TD ON
        (
            ALLOWED_LIST_ROWS.NPA = TD.NPA
            AND ALLOWED_LIST_ROWS.NXX = TD.NXX
            AND (ROW_NUMBER BETWEEN @startRecordNumber AND @endRecordNumber)
        )

    END
    ELSE
    --If we have filter criteria
    BEGIN
        SELECT @totalResults = COUNT(1)
        FROM
		(
        (
            SELECT NPA, NXX
            FROM Config.AllowedNPANXX
            WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
        )
        UNION ALL
        (
            SELECT NPA, NXX
            FROM Config.AllowedAni
            WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
        ) 
		) ALLOWED_LIST_COUNT
        --Filter by state
        JOIN TelcoData.NPANXX TD ON
        (
            ALLOWED_LIST_COUNT.NPA = TD.NPA
            AND ALLOWED_LIST_COUNT.NXX = TD.NXX
            AND (@stateFilter IS NULL OR TD.StateAbbr = @stateFilter)
        )
        
        --Calculate the starting record number
        SET @startRecordNumber = ((@pageNumber-1) * @recordsPerPage) + 1

        --If the start record number is past the total number of results returned
        IF(@startRecordNumber > @totalResults)
        BEGIN
            --Set the starting record number to the first record on the last page
            set @pageNumber = ceiling(cast(@totalResults as float) / @recordsPerPage)
            set @startRecordNumber = (@pageNumber - 1) * @recordsPerPage + 1
        END
        
        --Calculate the end record number. This might still be past the end of the record set, but it doesn't matter.
        SET @endRecordNumber = @pageNumber * @recordsPerPage
    
        --Get the filtered list and place it into a temp table
        SELECT
         ALLOWED_LIST_ROWS.NPA
        ,ALLOWED_LIST_ROWS.NXX
        ,ALLOWED_LIST_ROWS.XXXX
        ,CITY
        ,STATE
        FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ALLOWED_LIST.NPA, ALLOWED_LIST.NXX, XXXX) AS ROW_NUMBER
            ,ALLOWED_LIST.NPA
            ,ALLOWED_LIST.NXX
            ,ALLOWED_LIST.XXXX
            ,TD.CityName as CITY
            ,TD.StateAbbr as STATE
            FROM
            (
                --Filter by NPA and get the combined list of allowed ANIs and exchanges
                (
                    SELECT NPA, NXX, null as XXXX
                    FROM Config.AllowedNPANXX
                    WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
                )
                UNION ALL
                (
                    SELECT NPA, NXX, XXXX
                    FROM Config.AllowedAni
                    WHERE (@NPAFilter IS NULL OR NPA = @NPAFilter)
                )
            ) ALLOWED_LIST
            --Filter by state
            JOIN TelcoData.NPANXX TD ON
            (
                ALLOWED_LIST.NPA = TD.NPA
                AND ALLOWED_LIST.NXX = TD.NXX
                AND (@stateFilter IS NULL OR TD.StateAbbr = @stateFilter)
            )
        ) ALLOWED_LIST_ROWS
        WHERE (ROW_NUMBER BETWEEN @startRecordNumber AND @endRecordNumber)
    END
    RETURN 0
END

